import json
import pytz
import logging
import xlwt
import math
import werkzeug.exceptions
from datetime import datetime, timedelta, time
from itertools import groupby

from odoo import http
from odoo.addons.test_convert.tests.test_env import record
from odoo.http import request, content_disposition
from odoo.tools import DEFAULT_SERVER_DATE_FORMAT, DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import ValidationError
from odoo.tools.misc import html_escape

_logger = logging.getLogger(__name__)


class Controller(http.Controller):

    @http.route('/download/sb/production/daily/report/excel/<string:date_type>/<string:to_date>', type='http', auth='user', website=True)
    def download_production_daily_two_report_excel(self, date_type=None, to_date=None, **data):
        data = request.env['s.production.daily.report.two'].get_production_daily_data(to_date, date_type)
        book = xlwt.Workbook(encoding='utf-8')
        sheet = book.add_sheet('Sheet1')

        def create_style(font_height, center=True, borders=True):
            style = xlwt.XFStyle()
            font = xlwt.Font()
            font.height = font_height
            style.font = font
            if center:
                # 垂直居中 水平居中
                vertically_horizontally_centered = xlwt.Alignment()
                vertically_horizontally_centered.vert = xlwt.Alignment.VERT_CENTER
                vertically_horizontally_centered.horz = xlwt.Alignment.HORZ_CENTER
                style.alignment = vertically_horizontally_centered
            if borders:
                border = xlwt.Borders()
                border.bottom = border.top = border.left = border.right = xlwt.Borders.THIN
                style.borders = border
            return style

        field_style = create_style(15 * 15, center=True)
        header =  data.get('header', [])
        row = 0
        for i, item in enumerate(header):
            sheet.row(i).height_mismatch = True
            sheet.row(i).height = 300
            if i < 2 or i > (len(header) - 3):
                sheet.write(0, row, item, field_style)
                row += 1
            else:
                sheet.write_merge(0, 0, row, row + 1, item, field_style)
                row += 2

        ii = 1
        for item in data.get('lines', []):
            sheet.row(ii).height_mismatch = True
            sheet.row(ii).height = 300
            daily = item.get('daily', [])
            data_list = [item.get('name', ''), item.get('wip', 0)]
            for d in daily:
                data_list.append(d.get('qty', 0))
                data_list.append(d.get('area', 0))

            data_list.append(item.get('total_qty', 0))
            data_list.append(item.get('total_area', 0))
            data_list.append(item.get('average', 0))
            data_list.append(item.get('target_daily_production', 0))
            for index, v in enumerate(data_list):
                sheet.write(ii, index, v, field_style)

            ii += 1
        file_name = f'生产日报表.xls'
        response = request.make_response(None, headers=[
            ('Content-Type', 'application/vnd.ms-excel'),
            ('Content-Disposition', content_disposition(file_name))
        ])
        book.save(response.stream)
        return response

    @http.route('/download/production/daily/report/excel/<string:start>/<string:end>', type='http', auth='user', website=True)
    def download_production_daily_report_excel(self, start=None, end=None, **data):
        data = self._get_production_daily_report_data(start, end)
        book = xlwt.Workbook(encoding='utf-8')
        sheet = book.add_sheet('Sheet1')

        def create_style(font_height, center=True, borders=True):
            style = xlwt.XFStyle()
            font = xlwt.Font()
            font.height = font_height
            style.font = font
            if center:
                # 垂直居中 水平居中
                vertically_horizontally_centered = xlwt.Alignment()
                vertically_horizontally_centered.vert = xlwt.Alignment.VERT_CENTER
                vertically_horizontally_centered.horz = xlwt.Alignment.HORZ_CENTER
                style.alignment = vertically_horizontally_centered
            if borders:
                border = xlwt.Borders()
                border.bottom = border.top = border.left = border.right = xlwt.Borders.THIN
                style.borders = border
            return style

        field_style = create_style(15 * 15, center=True)
        for i, item in enumerate(data):
            sheet.row(i).height_mismatch = True
            sheet.row(i).height = 300
            for index, v in enumerate(item):
                sheet.write(i, index, v, field_style)

        # sheet.col(0).width = 256 * 20
        # sheet.col(1).width = 256 * 40

        user_tz = request.env.user.tz or 'UTC'
        timezone = pytz.timezone(user_tz)
        utc_time = pytz.utc.localize(datetime.now())
        local_time = utc_time.astimezone(timezone)
        file_name = f'生产日报表-{local_time.strftime(DEFAULT_SERVER_DATE_FORMAT)}.xls'
        response = request.make_response(None, headers=[
            ('Content-Type', 'application/vnd.ms-excel'),
            ('Content-Disposition', content_disposition(file_name))
        ])
        book.save(response.stream)
        return response

    def _get_production_daily_report_data(self, start, end):
        sql = f"""
            select DATE((mwl.create_date + INTERVAL '9 hours') AT TIME ZONE 'UTC') AS c_date,
                   mw.workcenter_id,
                   sum(mwl.qty_produced)
            from mrp_workorder mw
                     left join mrp_workorder_line mwl on mw.id = mwl.workorder_id
            
            where state = 'done'
              and mwl.create_date >= '{start}'
              and mwl.create_date <= '{end}'
            group by c_date, mw.workcenter_id
            order by c_date
        
        """
        request.env.cr.execute(sql)
        result = request.env.cr.fetchall()
        data_dict = {}

        # 遍历查询结果，填充字典，并将日期转换为字符串
        for c_date, workcenter_id, total_qty in result:
            # 将日期转换为字符串格式
            create_date_str = c_date.strftime('%Y-%m-%d')

            if create_date_str not in data_dict:
                data_dict[create_date_str] = {}
            data_dict[create_date_str][workcenter_id] = total_qty

        # 获取所有的 workcenter_id
        all_workcenters = set()
        for date_data in data_dict.values():
            all_workcenters.update(date_data.keys())

        all_workcenters = list(sorted(all_workcenters))
        all_workcenters_str = []
        for center in all_workcenters:
            center_id = request.env['mrp.workcenter'].browse(center)
            all_workcenters_str.append(center_id.name)
        # 将字典转换为二维数组
        headers = ['日期'] + all_workcenters_str
        pivot_data = [headers]  # 初始化表头

        for create_date_str in sorted(data_dict.keys()):
            row = [create_date_str]
            for workcenter_id in all_workcenters:
                row.append(data_dict[create_date_str].get(workcenter_id, 0))  # 如果没有对应的数据，默认为0
            pivot_data.append(row)

        return pivot_data

    def get_red_style(self, color):
        """ 获取红色背景 """
        style = xlwt.XFStyle()
        field_font = xlwt.Font()
        field_font.height = 15 * 15
        style.font = field_font
        borders = xlwt.Borders()
        borders.bottom = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN

        vertically_horizontally_centered = xlwt.Alignment()
        vertically_horizontally_centered.vert = xlwt.Alignment.VERT_CENTER
        vertically_horizontally_centered.horz = xlwt.Alignment.HORZ_CENTER

        style.borders = borders
        style.alignment = vertically_horizontally_centered
        pattern = xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        if color == 'red':
            pattern.pattern_fore_colour = xlwt.Style.colour_map['red']
        elif color == 'green':
            pattern.pattern_fore_colour = xlwt.Style.colour_map['green']
        style.pattern = pattern
        return style